Create PROCEDURE [dbo].[GetEmployee]

(
	@Page int,
	@PageSize int,
	@TotalNofRecords int output
)

AS
BEGIN
SET NOCOUNT ON
DECLARE @StartRecordNo int, @EndRecordNo int

IF (@Page=0)
	BEGIN
		SET @StartRecordNo = 1
		SET @EndRecordNo = @PageSize
	END
ELSE
	BEGIN
		SET @StartRecordNo = @PageSize * @Page + 1
		SET @EndRecordNo = (@PageSize * (@Page+1) )
	END

CREATE TABLE [#TmpEmployee]
(
	[RowNo] int identity(1,1),
	[EmployName] int not null,
	[Address] varchar(255)
)
INSERT INTO [#TmpEmployee]([EmployName], [Address])
	SELECT
		FirstName, Address1
	FROM Employee
	ORDER BY FirstName

SELECT @TotalNofRecords = COUNT( FirstName) FROM [#TmpEmployee]

SET NOCOUNT OFF
SELECT [RowNo], [FirstName], [Address]
FROM [#TmpEmployee]
WHERE [RowNo]>= @StartRecordNo
AND [RowNo]<= @EndRecordNo
RETURN 0

END